#include <stdio.h>
#include <error.h>
#include <stdlib.h>
#include <time.h>
#include <pthread.h>

#include "userSqliteOp.h"
#include "utility.h"
#include "log.h"

static int isTableExitCb(void *data, int argc, char **argv, char **azColName);

//创建用户�?
const char *SQL_COMMAND_CREAT_USER_TABLE = "CREATE TABLE %s ("
                                           "id UNSIGNED BIG INT PRIMARY KEY      NOT NULL, " //id,类似QQ账号,不可更改
                                           "name            char(64)    NOT NULL, "          //名字
                                           "nickname        char(64)    NOT NULL, "          //昵称
                                           "password        char(64)    NOT NULL, "          //密码
                                           "onlineState     INT         NOT NULL, "          //在线状�?
                                           "signature       char(128),            "          //�?性�?�名
                                           "birthday        DATE        NOT NULL, "          //生日,精确到天
                                           "registerTime    DATETIME    NOT NULL, "          //注册时间,精确到�??
                                           "hierarchy       INT         NOT NULL, "          //类似QQ等级
                                           "phoneNum        char(64), "                      //电话号码
                                           "address         char(128), "                     //住址
                                           "session			char(64), "                      //session,用于验证
                                           "lastActiveTime  SIGNED BIG INT, "                //最后一次活跃时�?
                                           "pictureLen      INT, "                           //头像文件的长�?
                                           "headPicture     TEXT);";                         //头像

const char *SQL_COMMAND_USER_INSERTTABLE = "INSERT INTO %s (id,name,nickname,password,onlineState,hierarchy,birthday,registerTime)"
                                           "VALUES(%llu, " //id,unsigned long long int
                                           "'%s', "        //name,
                                           "'%s', "        //nickname
                                           "'%s', "        //password
                                           "%d, "          //onlineState
                                           "%d, "          //hierarchy,QQ等级
                                           "%s,"           //birthday,生日
                                           "%s);";         //registerTime,注册时间
const char *SQL_COMMAND_USER_UPDATETTABLE = "UPDATE %s set name='%s', nickname='%s', password='%s', onlineState=%d ,hierarchy=%d, "
                                            "phoneNum='%s', birthday=date('%s') where id=%llu;";

extern pthread_mutex_t sqlite3OpenMutex;

void printUserInfo(USER *user)
{
    fprintf(stderr, "账号:%llu\n", (unsigned long long)user->id);
    fprintf(stderr, "姓名:%s\n", (char *)user->name);
    fprintf(stderr, "昵称:%s\n", (char *)user->nickname);
    fprintf(stderr, "生日:%s\n", (char *)user->birthday);
    fprintf(stderr, "注册时间:%s\n", (char *)user->registerTime);
    fprintf(stderr, "账号等级:%d\n", (int)user->hierarchy);
    if (strlen((char *)user->phoneNum) > 2)
        fprintf(stderr, "电话号码:%s\n", (char *)user->phoneNum);
    if (strlen((char *)user->address) > 2)
        fprintf(stderr, "住址:%s\n", (char *)user->address);
    fprintf(stderr, "\rinput command>");
}

/**
 * @brief  通过id删除用户
 * @note   
 * @param  id: 
 * @retval 
 */
int deleteUserById(u_int64_t id)
{
    char *zErrMsg = 0;
    char sql[MAX_MESSAGE_SIZE];
    sqlite3 *db = NULL;
    int ret;
    ret = my_sqlite3_open(DB_NAME, &db);
    UTIL_ASSERT(!ret);
    UTIL_ASSERT(db != NULL);

    bzero(sql, MAX_MESSAGE_SIZE);
    snprintf(sql, MAX_MESSAGE_SIZE, "DELETE from %s where id=%llu;", USER_TABLE_NAME, (long long unsigned int)id);
    ret = sqlite3_exec(db, sql, NULL, 0, &zErrMsg);
    if (ret != SQLITE_OK)
    {
        fprintf(stderr, "SQL error: %s\n", zErrMsg);
        sqlite3_free(zErrMsg);
        my_sqlite3_close(db);
        return FAILED;
    }
    fprintf(stdout, "Records deleted successfully\n");
    my_sqlite3_close(db);
    return SUCCESS;
}

/**
 * @brief  更新用户信息
 * @note   
 * @param  id: 
 * @param  *pUser: 
 * @retval 
 */
int updateUserInfoById(u_int64_t id, const USER *pUser)
{
    char *zErrMsg = 0;
    char sql[MAX_MESSAGE_SIZE];
    sqlite3 *db = NULL;
    int ret;
    ret = my_sqlite3_open(DB_NAME, &db);
    UTIL_ASSERT(!ret);
    UTIL_ASSERT(db != NULL && pUser != NULL);
    bzero(sql, MAX_MESSAGE_SIZE);
    snprintf(sql, MAX_MESSAGE_SIZE, SQL_COMMAND_USER_UPDATETTABLE, USER_TABLE_NAME, pUser->name, pUser->nickname,
             pUser->password, pUser->onlineState, pUser->hierarchy, pUser->phoneNum, pUser->birthday, id);
    ret = sqlite3_exec(db, sql, NULL, 0, &zErrMsg);
    if (ret != SQLITE_OK)
    {
        fprintf(stderr, "SQL error: %s\n", zErrMsg);
        sqlite3_free(zErrMsg);
        my_sqlite3_close(db);
        return FAILED;
    }
    my_sqlite3_close(db);
    fprintf(stdout, "Records created successfully\n");
    return SUCCESS;
}

int updateSession(u_int64_t id, char *session)
{
    char *zErrMsg = 0;
    char sql[MAX_MESSAGE_SIZE];
    sqlite3 *db = NULL;
    int ret;
    ret = my_sqlite3_open(DB_NAME, &db);
    UTIL_ASSERT(!ret);
    UTIL_ASSERT(db != NULL && session != NULL);
    bzero(sql, MAX_MESSAGE_SIZE);
    snprintf(sql, MAX_MESSAGE_SIZE, "UPDATE %s set session='%s',lastActiveTime = %lli where id=%llu;",
             USER_TABLE_NAME, session, (long long signed int)time(NULL), (long long unsigned int)id);

    ret = sqlite3_exec(db, sql, NULL, 0, &zErrMsg);
    if (ret != SQLITE_OK)
    {
        fprintf(stderr, "SQL error: %s\n", zErrMsg);
        sqlite3_free(zErrMsg);
        my_sqlite3_close(db);
        return FAILED;
    }
    my_sqlite3_close(db);
    fprintf(stdout, "Records created successfully\n");
    return SUCCESS;
}

static int insertUserInfoCb(void *NotUsed, int argc, char **argv, char **azColName)
{
    int i;
    for (i = 0; i < argc; i++)
    {
        printf("%s = %s\n", azColName[i], argv[i] ? argv[i] : "NULL");
    }
    printf("\n");
    return 0;
}
/**
 * @brief  向用户表插入一条�?�录
 * @note   注册时默认用户为离线，等级默认为0
 * @param  id: 账号(类似于QQ�?)，用户用户标识，�?一
 * @param  *name: 姓名
 * @param  *nickname: 昵称
 * @param  *passwd: 密码
 * @retval 
 */
int insertUserInfo(u_int64_t id, const char *name, const char *nickname, const char *passwd)
{
    char *zErrMsg = 0;
    char sql[MAX_MESSAGE_SIZE];
    sqlite3 *db = NULL;
    int ret;
    ret = my_sqlite3_open(DB_NAME, &db);
    UTIL_ASSERT(!ret);
    UTIL_ASSERT(db != NULL && name != NULL && nickname != NULL && passwd != NULL);

    bzero(sql, MAX_MESSAGE_SIZE);
    snprintf(sql, MAX_MESSAGE_SIZE, SQL_COMMAND_USER_INSERTTABLE, USER_TABLE_NAME, id, name, nickname, passwd, ENUM_OFF_LINE, 0, "date('2000-01-01')", "datetime('now')");
    ret = sqlite3_exec(db, sql, insertUserInfoCb, 0, &zErrMsg);
    if (ret != SQLITE_OK)
    {
        fprintf(stderr, "%s,SQL error: %s\n", __FUNCTION__, zErrMsg);
        sqlite3_free(zErrMsg);
        my_sqlite3_close(db);
        return FAILED;
    }
    my_sqlite3_close(db);
    fprintf(stdout, "Records created successfully\n");
    return SUCCESS;
}
static int searchUserByIdCb(void *data, int argc, char **argv, char **azColName)
{
    UTIL_ASSERT(data != NULL);
    bzero(data, sizeof(USER));
    USER *pUser = (USER *)data;
    pUser->id = atoll(argv[0]);
    if (argv[1])
    {
        memcpy(pUser->name, argv[1], strlen(argv[1]));
    }
    if (argv[2])
    {
        memcpy(pUser->nickname, argv[2], strlen(argv[2]));
    }
    if (argv[3])
    {
        memcpy(pUser->password, argv[3], strlen(argv[3]));
    }
    pUser->onlineState = atoi(argv[4]);
    if (argv[5])
    {
        memcpy(pUser->signature, argv[5], strlen(argv[5]));
    }
    if (argv[6])
    {
        memcpy(pUser->birthday, argv[6], strlen(argv[6]));
    }
    if (argv[7])
    {
        memcpy(pUser->registerTime, argv[7], strlen(argv[7]));
    }
    pUser->hierarchy = atoi(argv[8]);
    if (argv[9])
    {
        memcpy(pUser->phoneNum, argv[9], strlen(argv[9]));
    }
    if (argv[10])
    {
        memcpy(pUser->address, argv[10], strlen(argv[10]));
    }
    if (argv[11])
    {
        memcpy(pUser->session, argv[11], strlen(argv[11]));
    }
    return 0;
}
/**
 * @brief  通过用户id查找用户
 * @note   
 * @param  id: 
 * @param  *pUser: 保存查�?�的结果
 * @retval 成功返回0
 */
int searchUserById(unsigned long long id, USER *pUser)
{
    char *zErrMsg = 0;
    sqlite3 *db = NULL;
    int ret;
    ret = my_sqlite3_open(DB_NAME, &db);
    UTIL_ASSERT(!ret);
    UTIL_ASSERT(db != NULL && pUser != NULL);
    char sql[STRING_ELN];
    bzero(sql, STRING_ELN);
    snprintf(sql, STRING_ELN * 2, "select id,name,nickname,password,onlineState,signature,birthday,registerTime,hierarchy,phoneNum,address,session from %s where id = %llu;", USER_TABLE_NAME, id);
    ret = sqlite3_exec(db, sql, searchUserByIdCb, (void *)pUser, &zErrMsg);
    if (ret != SQLITE_OK)
    {
        fprintf(stderr, "SQL error: %s\n", zErrMsg);
        sqlite3_free(zErrMsg);
        my_sqlite3_close(db);
        return FAILED;
    }
    fprintf(stdout, "Operation done successfully\n");

    my_sqlite3_close(db);
    return SUCCESS;
}

/** 
 * getUserPasswordByName
 * @Descript:根据用户名获取密�?
 * @Author	:hezuoqiang
 * @DateTime:2019�?10�?31�?T18:59:51+0800
 * @param	name	:用户�?
 * @param	passwd	:由函数填写的明文密码
 * @param	id	:用户id�?
 * @RETURN VALUE 
 * @    On success, zero is returned.  On error, negative number is returned
 */
int getUserPasswordByName(char *name, char *passwd, u_int64_t *id)
{
    int ret;
    int nRow;              /* Number of result rows written here */
    int nColumn;           /* Number of result columns written here */
    char *pzErrmsg = NULL; /* Error msg written here */
    sqlite3 *db = NULL;
    char **dbResult;

    /* 1.连接数据�? */
    ret = my_sqlite3_open(DB_NAME, &db);
    UTIL_ASSERT(!ret);

    /* 2.构造查询条�? */
    char sqlcmd[STRING_ELN];
    bzero(sqlcmd, STRING_ELN);
    snprintf(sqlcmd, STRING_ELN, "select password,id from %s where name ='%s';", USER_TABLE_NAME, name);

    /* 3.获取结果 */
    ret = sqlite3_get_table(db, sqlcmd, &dbResult, &nRow, &nColumn, &pzErrmsg);
    if (ret != SQLITE_OK)
    {
        fprintf(stderr, "sqlite3_get_table err:%s\n", pzErrmsg);
        if (pzErrmsg != NULL)
        {
            sqlite3_free(pzErrmsg);
            pzErrmsg = NULL;
        }
        sqlite3_free_table(dbResult);
        my_sqlite3_close(db);
        return -1;
    }
    if (!nRow)
    {
        sqlite3_free_table(dbResult);
        my_sqlite3_close(db);
        return -1;
    }
    memcpy(passwd, dbResult[1 * nColumn + 0], MIN(strlen(dbResult[1 * nColumn]), NAME_PASSWD_SESSION_LEN));
#if _BSD_SOURCE || _SVID_SOURCE || _XOPEN_SOURCE >= 600 || _ISOC99_SOURCE || _POSIX_C_SOURCE >= 200112L
    *id = (u_int64_t)atol(dbResult[1 * nColumn + 1]);
#else
    *id = (u_int64_t)atoi(dbResult[1 * nColumn + 1]);
#endif
    sqlite3_free_table(dbResult);
    my_sqlite3_close(db);
    return 0;
}
/** 
 * getUserSessionById
 * @Descript:通过用户id获取用户的session与上一次的活跃时间
 * @Author	:hezuoqiang
 * @DateTime:2019�?11�?3�?T11:37:00+0800
 * @param	id	:
 * @param	session	:
 * @param	lastActiveTime	:
 * @RETURN VALUE 
 * @    On success, zero is returned.  On error, negative number is returned
 */
int getUserSessionById(u_int64_t id, char *session, int64_t *lastActiveTime)
{
    int ret;
    int nRow;              /* Number of result rows written here */
    int nColumn;           /* Number of result columns written here */
    char *pzErrmsg = NULL; /* Error msg written here */
    sqlite3 *db = NULL;
    char **dbResult;

    UTIL_ASSERT(session != NULL && lastActiveTime != NULL);

    /* 1.连接数据�? */
    ret = my_sqlite3_open(DB_NAME, &db);
    UTIL_ASSERT(!ret);

    /* 2.构造查询条�? */
    char sqlcmd[STRING_ELN];
    bzero(sqlcmd, STRING_ELN);
    snprintf(sqlcmd, STRING_ELN, "select session,lastActiveTime from %s where id = %llu;", USER_TABLE_NAME, (unsigned long long)id);

    /* 3.获取结果 */
    ret = sqlite3_get_table(db, sqlcmd, &dbResult, &nRow, &nColumn, &pzErrmsg);
    if (ret != SQLITE_OK)
    {
        fprintf(stderr, "sqlite3_get_table err:%s\n", pzErrmsg);
        if (pzErrmsg != NULL)
        {
            sqlite3_free(pzErrmsg);
            pzErrmsg = NULL;
        }
        sqlite3_free_table(dbResult);
        my_sqlite3_close(db);
        return -1;
    }
    if (!nRow)
    {
        my_sqlite3_close(db);
        return -1;
    }
    bzero(session, NAME_LEN);
    memcpy(session, dbResult[1 * nColumn + 0], MIN(strlen(dbResult[1 * nColumn + 0]), NAME_LEN));
#if _BSD_SOURCE || _SVID_SOURCE || _XOPEN_SOURCE >= 600 || _ISOC99_SOURCE || _POSIX_C_SOURCE >= 200112L
    *lastActiveTime = (u_int64_t)atol(dbResult[1 * nColumn + 1]);
#else
    *lastActiveTime = (u_int64_t)atoi(dbResult[1 * nColumn + 1]);
#endif
    sqlite3_free_table(dbResult);
    my_sqlite3_close(db);
    return 0;
}

/** 
 * getMaxUserId
 * @Descript:
 * @Author	:hezuoqiang
 * @DateTime:2019�?10�?31�?T17:23:07+0800
 * @param	id	:
 * @RETURN VALUE 
 * @    On success, zero is returned.  On error, negative number is returned
 */
int getMaxUserId(u_int64_t *id)
{
    int ret;
    int nRow;              /* Number of result rows written here */
    int nColumn;           /* Number of result columns written here */
    char *pzErrmsg = NULL; /* Error msg written here */
    sqlite3 *db = NULL;
    char **dbResult;

    /* 1.连接数据�? */
    ret = my_sqlite3_open(DB_NAME, &db);
    UTIL_ASSERT(!ret);

    /* 2.构造查询条�? */
    char sqlcmd[STRING_ELN];
    bzero(sqlcmd, STRING_ELN);
    snprintf(sqlcmd, STRING_ELN, "select MAX(id) from %s;", USER_TABLE_NAME);

    /* 3.获取结果 */
    ret = sqlite3_get_table(db, sqlcmd, &dbResult, &nRow, &nColumn, &pzErrmsg);
    if (ret != SQLITE_OK)
    {
        fprintf(stderr, "sqlite3_get_table err:%s\n", pzErrmsg);
        if (pzErrmsg != NULL)
        {
            sqlite3_free(pzErrmsg);
            pzErrmsg = NULL;
        }
        sqlite3_free_table(dbResult);
        my_sqlite3_close(db);
        return -1;
    }
    if (!dbResult[1])
    {
        *id = 0;
        my_sqlite3_close(db);
        return 0;
    }
#if _BSD_SOURCE || _SVID_SOURCE || _XOPEN_SOURCE >= 600 || _ISOC99_SOURCE || _POSIX_C_SOURCE >= 200112L
    *id = (u_int64_t)atol(dbResult[1]);
#else
    *id = (u_int64_t)atoq(dbResult[1]);
#endif
    sqlite3_free_table(dbResult);
    my_sqlite3_close(db);
    return 0;
}

/**
 * @brief  判断表是否存在的回调函数
 * @note   
 * @param  *data: 函数的隐式返回�?
 * @param  argc: 
 * @param  **argv: 
 * @param  **azColName: 
 * @retval 
 */
int isTableExitCb(void *data, int argc, char **argv, char **azColName)
{
    int *retval = (int *)data;
    if (argv[0] && atoi(argv[0]))
        *retval = TABLE_EXITED;
    else
        *retval = TABLE_NOT_EXITED;
    return 0;
}

/**
 * @brief  判断表是否存�?
 * @note   
 * @param  *db: 数据库句�?
 * @param  *tableName: 表名
 * @retval 
 */
int isTableExit(sqlite3 *db, const char *tableName)
{
    UTIL_ASSERT(db != NULL && tableName != NULL);
    int rc = 0;
    char *zErrMsg = NULL;
    char sql[STRING_ELN];
    int is_table_exit = 0;

    snprintf(sql, STRING_ELN, "select count(*)  from sqlite_master where type='table' and name = '%s';", tableName);
    rc = sqlite3_exec(db, sql, isTableExitCb, (void *)&is_table_exit, &zErrMsg);
    UTIL_ASSERT(rc == SQLITE_OK);

    return is_table_exit;
}

static int createUserTableCb(void *NotUsed, int argc, char **argv, char **azColName)
{
    int i;
    printf("argc = %d\n", argc);
    for (i = 0; i < argc; i++)
    {
        printf("%s = %s\n", azColName[i], argv[i] ? argv[i] : "NULL");
    }
    printf("\n");
    return 0;
}
/**
 * @brief  创建�?
 * @note   
 * @param  *db: 数据库句�?
 * @param  *tableName: 表名
 * @param  *sql: sql�?�?
 * @retval 
 */
int createTable(sqlite3 *db, const char *tableName, const char *sql)
{
    UTIL_ASSERT(db != NULL && tableName != NULL);
    char *zErrMsg = 0;
    int rc;
    char sqlcmd[1024];
    bzero(sqlcmd, 1024);
    snprintf(sqlcmd, 1024, sql, tableName);
    printf("%s\n", sqlcmd);
    /* Execute SQL statement */
    rc = sqlite3_exec(db, sqlcmd, createUserTableCb, 0, &zErrMsg);
    if (rc != SQLITE_OK)
    {
        fprintf(stderr, "SQL error: %s\n", zErrMsg);
        sqlite3_free(zErrMsg);
    }
    else
    {
        fprintf(stdout, "Table created successfully\n");
        return SUCCESS;
    }
    return FAILED;
}
